package com.hst.capacity.infra.persistence.jpa.order.intercity;

import com.hst.capacity.domain.model.entity.order.express.ExpressOrderEntity;
import com.hst.capacity.domain.model.entity.order.intercity.IntercityOrderEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;

public interface IcOrderRepository extends JpaRepository<IntercityOrderEntity, String>, JpaSpecificationExecutor<IntercityOrderEntity> {

    IntercityOrderEntity findByIdOrder(String IdOrder);

    @Query("select t from IntercityOrderEntity t where t.externalOrderId = ?1")
    IntercityOrderEntity findByExternalOrderId(String IdOrder);

    @Query("select t from IntercityOrderEntity t where t.idOrder in (:listIdOrder)")
    List<IntercityOrderEntity> findByIdOrderList(List<String> listIdOrder);

    /**
     * 根据下单人手机查找订单
     * @param userMobile
     * @param pageable
     * @return
     */
    List<IntercityOrderEntity> findByUserMobile(String userMobile, Pageable pageable);

    /**
     * 新订单列表
     * @param idFranchisee
     * @param status
     * @param source
     * @param payStatus
     * @param pageable
     * @return
     */
    @Query("select t from IntercityOrderEntity t where t.idFranchisee= :idf and (t.status in (:sta) and  t.orderSource in (:sou) and t.paymentStatus in(:pay) or 1=1) order by t.appointmentTime desc")
    Page<IntercityOrderEntity> findNewOrder(@Param("idf")String idFranchisee, @Param("sta")List<Integer> status, @Param("sou")List<Integer> source, @Param("pay") List<Integer> payStatus, Pageable pageable);

    /**
     * 更新订单价格
     * @param price
     * @param idOrder
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query("update IntercityOrderEntity t set t.estimatedMoney = ?1 where t.idOrder = ?2")
    int updatePrice(Float price,String idOrder);

    /**
     * 更新订单司机
     * @param idDriver
     * @param idOrder
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query("update IntercityOrderEntity t set t.idDriver =:idDriver " +
            ", t.idFleet =:idFleet " +
            ", t.driverInfo =:driverInfo " +
            ", t.status =:status " +
            ", t.plateNumber =:plateNumber " +
            ", t.vehicleInfo =:vehicleInfo " +
            ", t.assignTime = :assignTime " +
            ", t.virtualOrderAccepted = 0 " +
            "where t.idOrder =:idOrder ")
    int updateDriver(@Param("idDriver") String idDriver, @Param("idFleet") String idFleet,
                     @Param("driverInfo") String driverInfo, @Param("status") int status,
                     @Param("plateNumber") String plateNumber, @Param("vehicleInfo") String vehicleInfo,
                     @Param("assignTime") LocalDateTime assignTime, @Param("idOrder") String idOrder);

    /**
     * 更新订单司机
     * @param orderStatus
     * @param idOrder
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query("update IntercityOrderEntity t set t.status = ?2 where t.idOrder = ?1")
    int updateOrderStatus(String idOrder,Integer orderStatus);

    /**
     * 更新订单状态
     * @param orderStatus
     * @param idOrder
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query("update IntercityOrderEntity t set t.status = ?2,t.updateBy = ?3 where t.idOrder = ?1")
    int updateOrderStatus(String idOrder,Integer orderStatus,String updateBy);

    List<IntercityOrderEntity> findByIdLineAndStatus(String idLine,Integer status);
    List<IntercityOrderEntity> findByIdFranchiseeAndStatus(String idFranchisee,Integer status);

    /**
     * 根据城际线路代码查找订单
     * @param idCityLine
     * @return
     */
    @Query("select count(o) " +
            "from IntercityOrderEntity o " +
            "inner join IntercityLineEntity a on a.idLine = o.idLine " +
            "inner join CityLineEntity b on b.idCityLine = a.idCityLine " +
            "where o.status in(0,1) " +
            "and (o.orderType =0 or (o.orderType =1 and o.appointmentTime >= :hoursAgo))" +
            "and b.idCityLine=:idCityLine")
    int findCountByIdCityLine(@Param("idCityLine")String idCityLine, @Param("hoursAgo") LocalDateTime hoursAgo);

    /**
     * 根据城际线路代码查找订单
     * @param idCityLine
     * @return
     */
    @Query("select o " +
            "from IntercityOrderEntity o " +
            "inner join IntercityLineEntity a on a.idLine = o.idLine " +
            "inner join CityLineEntity b on b.idCityLine = a.idCityLine " +
            "where (o.status in(0,1) or (o.status = 2 and o.virtualOrderAccepted = 1))" +
            "and (o.orderType =0 or (o.orderType =1 and o.appointmentTime >= :hoursAgo))" +
            "and b.idCityLine=:idCityLine")
    List<IntercityOrderEntity> findByIdCityLine(@Param("idCityLine")String idCityLine, @Param("hoursAgo") LocalDateTime hoursAgo);

    /**
     * 根据城际线路代码查找订单件数
     * @param idLine
     * @return
     */
    @Query("select count (t) from IntercityOrderEntity t where t.idLine=:idLine and (t.status in(0,1) or (t.status = 2 and t.virtualOrderAccepted = 1)) and (t.orderType =0 or (t.orderType =1 and t.appointmentTime >= :hoursAgo))")
    int findCountByIdLine(@Param("idLine")String idLine, @Param("hoursAgo") LocalDateTime hoursAgo);

    /**
     * 根据司机代码查找订单
     * @param idDriver
     * @oaram idTask
     * @return
     */
    @Query("select t from IntercityOrderEntity t where t.idDriver=:idDriver and t.idTask =:idTask and t.status between 0 and 5")
    List<IntercityOrderEntity> findByIdDriverAndIdTask(@Param("idDriver")String idDriver,@Param("idTask")String idTask);

    /**
     * 根据司机代码查找预约订单
     * @param idDriver
     * @return
     */
    @Query("select t from IntercityOrderEntity t where t.idDriver=:idDriver and (t.idTask is null or t.idTask ='') and t.status =2 and t.virtualOrderAccepted = 0")
    List<IntercityOrderEntity> findAppointOrderByIdDriver(@Param("idDriver")String idDriver);

    /**
     * 更新订单状态任务ID
     * @param idTask
     * @param idOrder
     * @return
     */
    @Modifying
    @Query("update IntercityOrderEntity t set t.idTask = ?2,t.updateBy = ?3 where t.idOrder = ?1")
    int updateIdTask(String idOrder,String idTask,String updateBy);

    /**
     * 根据任务ID查找订单件数
     * @param idTask
     * @return
     */
    @Query("select count (t) from IntercityOrderEntity t where t.idTask=:idTask and t.status between 1 and 5")
    int findCountByIdTask(@Param("idTask")String idTask);

    /**
     * 更新订单系统派单时间
     * @param assignTime
     * @param idOrder
     * @return
     */
    @Modifying
    @Query("update IntercityOrderEntity t set t.assignTime = ?2 where t.idOrder = ?1")
    int updateAssignTime(String idOrder,LocalDateTime assignTime);

    /**
     * 任务有效订单
     * @oaram idTask
     * @return
     */
    @Query("select t from IntercityOrderEntity t where t.idTask =:idTask and t.status in(0,1,2,3,4,5,6,7)")
    List<IntercityOrderEntity> findIcOrderInService(@Param("idTask")String idTask);

    /**
     * 更新订单用车类型（0：实时 1：预约）
     * @param orderType
     * @param idOrder
     * @return
     */
    @Modifying
    @Query("update IntercityOrderEntity t set t.orderType = ?2 where t.idOrder = ?1")
    int updateOrderType(String idOrder,int orderType);

    /**
     * 司机抢单更新订单状态
     * @param idDriver
     * @param status
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query("update IntercityOrderEntity t set t.idDriver = ?2,t.idTask = ?3,t.status = ?4,t.plateNumber = ?5,t.vehicleInfo = ?6,t.updateBy = ?7 where t.idOrder = ?1")
    int driverGrabOrder(String idOrder, String idDriver, String idTask, Integer status, String plateNumber, String vehicleInfo, String updateBy);

    /**
     * 根据城际线路代码查找订单
     * @param idCityLine
     * @return
     */
    @Query("select count(o) " +
            "from IntercityOrderEntity o " +
            "inner join IntercityLineEntity a on a.idLine = o.idLine " +
            "inner join CityLineEntity b on b.idCityLine = a.idCityLine " +
            "where (o.status in(0,1) or (o.status = 2 and o.virtualOrderAccepted = 1))" +
            "and (o.orderType =0 or (o.orderType =1 and o.appointmentTime >= :appointmentTimeFrom and o.appointmentTime <= :appointmentTimeTo))" +
            "and b.idCityLine=:idCityLine")
    int findCountByIdCityLine(@Param("idCityLine")String idCityLine,
                              @Param("appointmentTimeFrom") LocalDateTime appointmentTimeFrom,
                              @Param("appointmentTimeTo") LocalDateTime appointmentTimeTo);

    /**
     * 根据城际线路代码查找订单
     * @param idCityLine
     * @return
     */
    @Query("select o " +
            "from IntercityOrderEntity o " +
            "inner join IntercityLineEntity a on a.idLine = o.idLine " +
            "inner join CityLineEntity b on b.idCityLine = a.idCityLine " +
            "where (o.status in(0,1) or (o.status = 2 and o.virtualOrderAccepted = 1))" +
            "and (o.orderType =0 or (o.orderType =1 and o.appointmentTime >= :appointmentTimeFrom and o.appointmentTime <= :appointmentTimeTo))" +
            "and b.idCityLine=:idCityLine")
    List<IntercityOrderEntity> findByIdCityLine(@Param("idCityLine")String idCityLine,
                                                @Param("appointmentTimeFrom") LocalDateTime appointmentTimeFrom,
                                                @Param("appointmentTimeTo") LocalDateTime appointmentTimeTo);

    /**
     * 根据城际线路代码查找订单件数
     * @param idLine
     * @return
     */
    @Query("select count (t) from IntercityOrderEntity t where t.idLine=:idLine and (t.status in(0,1) or (t.status = 2 and t.virtualOrderAccepted = 1)) and (t.orderType =0 or (t.orderType =1 and t.appointmentTime >= :appointmentTimeFrom and t.appointmentTime <= :appointmentTimeTo))")
    int findCountByIdLine(@Param("idLine")String idLine,
                          @Param("appointmentTimeFrom") LocalDateTime appointmentTimeFrom,
                          @Param("appointmentTimeTo") LocalDateTime appointmentTimeTo);

    /**
     * 根据司机ID统计未分账金额
     * @param idFranchisee
     * @return
     */
    @Query("select t from IntercityOrderEntity t where t.idFranchisee = :idFranchisee and t.virtualOrderAccepted = 0 and (t.appointmentTime >= :startTime and t.cutOffTime <= :endTime)")
    List<IntercityOrderEntity> dailyStat(@Param("idFranchisee")String idFranchisee, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);


    /**
     * 根据司机ID统计未分账金额
     * @param idFranchisee
     * @return
     */
    @Query("select t from IntercityOrderEntity t where t.idFranchisee = :idFranchisee and (t.status < 2 or (t.status = 2 and t.virtualOrderAccepted = 1)) and t.appointmentTime <= :remindTime")
    List<IntercityOrderEntity> orderReminder(@Param("idFranchisee")String idFranchisee, @Param("remindTime") LocalDateTime remindTime);


}
